﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.Util;
using NPOI.SS.UserModel;
using System.Text.RegularExpressions;
using System.Web;
using NPOI.HPSF;
using NPOI.SS.Util;
namespace MTQ.BLL.UploadExcel
{
    public class ExcelHelper
    {
        public delegate void ImportExcelDelegate(IWorkbook woorbook, DataTable dt, out string errorage);
        public static void UploadExcel2007(string fileExtension, Stream stream, ImportExcelDelegate import,DataTable dt, out string Errormessage)
        {
            string message = string.Empty;
            if (fileExtension == ".xls".ToLower())
            {
                IWorkbook hssfworkbook = new HSSFWorkbook(stream);

                import.Invoke(hssfworkbook, dt, out message);
            }
            else
            {
                IWorkbook hssfworkbook = new XSSFWorkbook(stream);

                import.Invoke(hssfworkbook, dt, out message);
            }
            Errormessage = message;

        }

        public delegate void ImportExcelDelegateDataset(IWorkbook woorbook, DataTable dt, DataSet ds, out string errorage);
        public static void UploadExcel2007DataSet(string fileExtension, Stream stream, ImportExcelDelegateDataset import, DataTable dt, DataSet ds, out string Errormessage)
        {
            string message = string.Empty;
            if (fileExtension == ".xls".ToLower())
            {
                IWorkbook hssfworkbook = new HSSFWorkbook(stream);

                import.Invoke(hssfworkbook, dt, ds, out message);
            }
            else
            {
                IWorkbook hssfworkbook = new XSSFWorkbook(stream);

                import.Invoke(hssfworkbook, dt, ds, out message);
            }
            Errormessage = message;

        }

        public static DataTable Convert2003ToDataTable(string path)
        {
            DataSet ds = new DataSet();
            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
                ISheet sheet = hssfworkbook.GetSheetAt(0);
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                DataTable dt = new DataTable();
                for (int j = 0; j < 10; j++)
                {
                    dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
                }
                while (rows.MoveNext())
                {
                    IRow row = (HSSFRow)rows.Current;
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        ICell cell = (HSSFCell)row.GetCell(i);
                        if (cell == null)
                        {
                            dr[i] = null;
                        }
                        else
                        {
                            dr[i] = cell.ToString();
                        }
                    }
                    dt.Rows.Add(dr);
                }
                ds.Tables.Add(dt);
            }
            return ds.Tables[0];
        }
        public static MemoryStream ExportFromDataTable(DataTable dtSource, List<string> rowName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
            HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            //取得列宽
            int[] arrColWidth = new int[dtSource.Columns.Count];
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            HSSFRow rowTitle = sheet.CreateRow(0) as HSSFRow;
            for (int i = 0; i < rowName.Count; i++)
            {
                rowTitle.CreateCell(i).SetCellValue(rowName[i]);
            }
            int rowIndex = 1;
            foreach (DataRow row in dtSource.Rows)
            {
                HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
                foreach (DataColumn column in dtSource.Columns)
                {
                    HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
                    if (column.ColumnName == "RecommendedTender")
                    {
                        CellRangeAddressList regions = new CellRangeAddressList(0, 65535, column.Ordinal, column.Ordinal);
                        DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "是", "否" });
                        HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
                        sheet.AddValidationData(dataValidate);
                    }
                    if (column.ColumnName == "Result")
                    {
                        CellRangeAddressList regions = new CellRangeAddressList(0, 65535, column.Ordinal, column.Ordinal);
                        DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "入围", "未入围"});
                        HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
                        sheet.AddValidationData(dataValidate);
                    }
                    string drValue = row[column].ToString();
                    switch (column.DataType.ToString())
                    {
                        case "System.String": //字符串类型
                            //double result;
                            //if (isNumeric(drValue, out result))
                            //{
                            //    double.TryParse(drValue, out result);
                            //    newCell.SetCellValue(result);
                            //    break;
                            //}
                            //else
                            //{
                            //    newCell.SetCellValue(drValue);
                            //    break;
                            //}
                            newCell.SetCellValue(drValue);
                            break;
                        case "System.Guid":
                            Guid guid;
                            Guid.TryParse(drValue, out guid);
                            newCell.SetCellValue(guid.ToString());
                            break;
                        case "System.DateTime": //日期类型
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV);

                            newCell.CellStyle = dateStyle; //格式化显示
                            break;
                        case "System.Boolean": //布尔型
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            break;
                        case "System.Int16": //整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue("");
                            break;
                        case "System.Decimal": //浮点型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            if (doubV == 0)
                            {
                                newCell.SetCellValue("");
                            }
                            else
                            {
                                var val = Math.Round(Convert.ToDecimal(doubV), 2).ToString("0.00");
                                newCell.SetCellValue(val);
                            }
                            break;
                        case "System.DBNull": //空值处理
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }

                }
                rowIndex++;
            }
            for (int i = 0; i < rowName.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dtSource"></param>
        /// <returns></returns>
        public static byte[] DataTableToByte(DataTable dtSource, List<string> list)
        {
            MemoryStream ms = ExcelHelper.ExportFromDataTable(dtSource, list);
            byte[] data = ms.ToArray();
            ms.Flush();
            return data;
        }

        public static bool isNumeric(string message, out double result)
        {
            Regex rex = new Regex(@"^[-]?\d+[.]?\d*$");
            result = -1;
            if (rex.IsMatch(message))
            {
                result = double.Parse(message);
                return true;
            }
            else
                return false;

        }
        //public static void ExportToExcel(DataTable dtSource,string strFileName)
        //{
        //    using (MemoryStream ms = ExportFromDataTable(dtSource))
        //    {
        //        using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
        //        {
        //            byte[] data = ms.ToArray();
        //            fs.Write(data, 0, data.Length);
        //            fs.Flush();
        //        }
        //    }
        //}

    }


}
